#!/usr/bin/env python3
"""
检查数据库中竞赛的图片字段内容
"""
import asyncio
import sqlite3
from pathlib import Path

async def check_image_fields():
    """
    检查数据库中竞赛的image_url和banner_image字段
    """
    print("=== 检查竞赛图片字段 ===")
    
    # 查找数据库文件
    db_paths = [
        "hackplay.db",
        "data/hackplay.db",
        "app.db"
    ]
    
    db_path = None
    for path in db_paths:
        if Path(path).exists():
            db_path = path
            break
    
    if not db_path:
        print("❌ 未找到数据库文件")
        return
    
    print(f"📁 使用数据库: {db_path}")
    
    try:
        # 连接数据库
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # 检查表结构
        cursor.execute("PRAGMA table_info(competitions)")
        columns = cursor.fetchall()
        
        print("\n📋 表结构:")
        for col in columns:
            if 'image' in col[1].lower():
                print(f"  - {col[1]} ({col[2]})")
        
        # 查询前10个竞赛的图片字段
        query = """
        SELECT 
            id, title, image_url, banner_image, logo_image
        FROM competitions 
        ORDER BY id 
        LIMIT 10
        """
        
        cursor.execute(query)
        competitions = cursor.fetchall()
        
        print(f"\n🔍 前10个竞赛的图片字段内容:")
        print("-" * 80)
        
        for comp in competitions:
            comp_id, title, image_url, banner_image, logo_image = comp
            print(f"\nID: {comp_id}")
            print(f"标题: {title[:50]}{'...' if len(title) > 50 else ''}")
            print(f"image_url: {image_url or '(空)'}")
            print(f"banner_image: {banner_image or '(空)'}")
            print(f"logo_image: {logo_image or '(空)'}")
            
            # 检查图片URL类型
            if banner_image:
                if 'trae-api-us.mchost.guru' in banner_image:
                    print("  📸 类型: Trae API生成图片")
                elif banner_image.startswith('http'):
                    print("  🌐 类型: 外部图片URL")
                elif banner_image.startswith('/'):
                    print("  📁 类型: 本地图片路径")
                else:
                    print("  ❓ 类型: 未知格式")
        
        # 统计图片字段使用情况
        cursor.execute("SELECT COUNT(*) FROM competitions WHERE image_url IS NOT NULL AND image_url != ''")
        image_url_count = cursor.fetchone()[0]
        
        cursor.execute("SELECT COUNT(*) FROM competitions WHERE banner_image IS NOT NULL AND banner_image != ''")
        banner_image_count = cursor.fetchone()[0]
        
        cursor.execute("SELECT COUNT(*) FROM competitions")
        total_count = cursor.fetchone()[0]
        
        print(f"\n📊 统计信息:")
        print(f"总竞赛数: {total_count}")
        print(f"有image_url的竞赛: {image_url_count}")
        print(f"有banner_image的竞赛: {banner_image_count}")
        
        conn.close()
        
    except Exception as e:
        print(f"❌ 数据库查询失败: {e}")

if __name__ == "__main__":
    asyncio.run(check_image_fields())